﻿using System;
using System.Data;
using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelTools.COMInterop
{
    public class ExcelInterop
    {
        private Excel.Workbook workbook;
        private Excel.Application application;

        public ExcelInterop()
        {
            application = new Excel.Application();
            workbook = null;
        }

        ~ExcelInterop()
        {
            if (WorkbookOpen)
                workbook.Close();

            application.Quit();
        }

        public void QuitApplication()
        {
            application.Quit();
        }

        public bool WorkbookOpen
        {
            get { return (workbook != null); }
        }

        public void OpenWorkbook(string filename)
        {
            if (WorkbookOpen)
                throw new InvalidOperationException("A workbook is already open in this application");

            workbook = application.Workbooks.Open(filename);
        }

        public DataTable ScanWorksheetToDataTable(string sheetName)
        {
            if (!WorkbookOpen)
                throw new InvalidOperationException("No workbook is open in this application");

            Excel.Worksheet sheet;
            int numSheets = workbook.Sheets.Count;

            for (int x = 1; x <= numSheets; x++)
            {
                sheet = (Excel.Worksheet)workbook.Sheets[x];
                if (sheetName.Equals(sheet.Name, StringComparison.CurrentCultureIgnoreCase))
                    return ScanWorksheetToDataTable(sheet);
            }

            return null;
        }

        public static DataTable ScanWorksheetToDataTable(Excel.Worksheet sheet)
        {
            object[,] data = ScanWorksheet(sheet);
            int rows = data.GetLength(0);
            int cols = data.GetLength(1);
            int r,c;
            object[] currentRow;
            DataTable result = new DataTable();
            DataColumn currentColumn;
            Type newType, oldType;

            for (c = 1; c <= cols; ++c)
            {
                result.Columns.Add((string)data[1, c],typeof(System.Object));
            }

            for (r = 2; r <= rows; ++r)
            {
                currentRow = new object[cols];

                for (c = 1; c <= cols; ++c)
                {
                    currentColumn = result.Columns[c-1];
                    oldType = currentColumn.DataType;
                    newType = data[r, c].GetType();

                    // If we have not yet determined the type of this column, then determine it now
                    if (oldType == typeof(System.Object))
                        currentColumn.DataType = newType;

                    // Otherwise, if the type of this field entry does not match what we expect,
                    // raise an exception
                    else if (oldType != newType)
                        throw new FormatException(String.Format("Inconsistent datatype in column {0}", currentColumn.ColumnName));

                    currentRow[c-1] = data[r, c];
                }

                result.Rows.Add(currentRow);
            }

            return result;
        }

        public static object[,] ScanWorksheet(Excel.Worksheet sheet)
        {
            return (object[,])sheet.UsedRange.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);
        }

        public object[,] ScanWorksheet(string sheetName)
        {
            if (!WorkbookOpen)
                throw new InvalidOperationException("No workbook is open in this application");

            Excel.Worksheet sheet;
            int numSheets = workbook.Sheets.Count;

            for (int x = 1; x <= numSheets; x++)
            {
                sheet = (Excel.Worksheet)workbook.Sheets[x];
                if (sheetName.Equals(sheet.Name, StringComparison.CurrentCultureIgnoreCase))
                    return ScanWorksheet(sheet);
            }

            return null;
        }

        public void CloseWorkbook()
        {
            if (!WorkbookOpen)
                throw new InvalidOperationException("No workbook is open in this application");

            workbook.Close();
            workbook = null;
        }
    }
}
